﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BIP;
using yunbo.Model.BIQI;

namespace yunbo.Repositorys.BIQIRepositorys
{
	public class QMS_ZJXMRepository
	{
		public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User Id=sa;Password=geili@2025;TrustServerCertificate=true;";


		/// <summary>
		/// 获取质检项目
		/// </summary>
		/// <param name="state"></param>
		/// <param name="str"></param>
		/// <returns></returns>
		public List<QMS_ZJXM> GetQMS_ZJXM(int state, string? str)
		{
			List<QMS_ZJXM> zjxm = new List<QMS_ZJXM>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select zjxm_id, zjxm_code, zjxm_name, zjxm_sx, [zjxm_dw],
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=zjxm_sx) zjxm_sx_name,
(select [dw_name] from [dbo].[bd_dw] where [dw_id]=[zjxm_dw]) zjxm_dw_name,
zjxm_jybz,zjxm_max,zjxm_min, zjxm_remark, zjxm_state
from [dbo].[qms_zjxm] where [zjxm_isdelete]=1 and [zjxm_state]=@state";
				if (str != null)
				{
					sql += @" and ([zjxm_code] like @str or [zjxm_name] like @str) ";
				}

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					command.Parameters.AddWithValue("@state", state);
					if (str != null)
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							zjxm.Add(new QMS_ZJXM
							{
								zjxm_id = Convert.ToInt32(reader["zjxm_id"]),
								zjxm_code = reader["zjxm_code"].ToString(),
								zjxm_name = reader["zjxm_name"].ToString(),
								zjxm_sx = Convert.ToInt32(reader["zjxm_sx"]),
								zjxm_dw = Convert.ToInt32(reader["zjxm_dw"]),
								zjxm_sx_name = reader["zjxm_sx_name"].ToString(),
								zjxm_dw_name = reader["zjxm_dw_name"].ToString(),
								zjxm_jybz = reader["zjxm_jybz"].ToString(),
								zjxm_max = reader["zjxm_max"].ToString(),
								zjxm_min = reader["zjxm_min"].ToString(),
								zjxm_remark = reader["zjxm_remark"].ToString(),
								zjxm_state = Convert.ToInt32(reader["zjxm_state"])
							});
						}
					}
				}
			}
			return zjxm;
		}


		/// <summary>
		/// 绑定质检项目下拉框
		/// </summary>
		/// <returns></returns>
		public List<QMS_ZJXM> GetOneQMS_ZJXM()
		{

			List<QMS_ZJXM> zjxm = new List<QMS_ZJXM>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select [zjxm_id],[zjxm_code],[zjxm_name] from [dbo].[qms_zjxm]
where  [zjxm_isdelete]=1 and [zjxm_state]=1";

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							zjxm.Add(new QMS_ZJXM
							{
								zjxm_id = Convert.ToInt32(reader["zjxm_id"]),
								zjxm_code = reader["zjxm_code"].ToString(),
								zjxm_name = reader["zjxm_name"].ToString()
							});
						}
					}
				}
			}
			return zjxm;
		}


		/// <summary>
		/// 质检项目列表弹窗
		/// </summary>
		/// <param name="codes"></param>
		/// <returns></returns>
		public List<QMS_ZJXM> GetZJXMs(string? codes)
		{
			List<QMS_ZJXM> zjxm = new List<QMS_ZJXM>();

			// 处理空或null的输入
			if (string.IsNullOrEmpty(codes))
			{
				return zjxm;
			}

			// 清理和分割codes，移除空项和空格
			var codeList = codes.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
								.Select(s => s.Trim())
								.Where(s => !string.IsNullOrEmpty(s))
								.ToList();

			// 如果没有有效的code，直接返回空列表
			if (codeList.Count == 0)
			{
				return zjxm;
			}

			// 判断是否需要查询全部记录（codes='0'）
			bool shouldQueryAll = codeList.Count == 1 && codeList[0] == "0";

			using (SqlConnection connection = new SqlConnection(data))
			{
				try
				{
					// 基础SQL查询
					string sql = @"SELECT [zjxm_id], [zjxm_name], zjxm_code,
                (SELECT dictionary_name FROM dictionary WHERE dictionary_id=[zjxm_sx]) zjxm_sx_name,
                [zjxm_jybz], [zjxm_remark] 
           FROM [dbo].[qms_zjxm] 
           WHERE [zjxm_state]=1 
               AND [zjxm_isdelete]=1";

					// 如果不需要查询全部记录，添加IN子句
					if (!shouldQueryAll)
					{
						sql += " AND [zjxm_code] IN (SELECT value FROM STRING_SPLIT(@codes, ',') WHERE value IS NOT NULL)";
					}

					connection.Open();

					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						// 如果不需要查询全部记录，添加参数
						if (!shouldQueryAll)
						{
							command.Parameters.AddWithValue("@codes", string.Join(",", codeList));
						}

						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
							{
								zjxm.Add(new QMS_ZJXM
								{
									zjxm_id = Convert.ToInt32(reader["zjxm_id"]),
									zjxm_code = reader["zjxm_code"].ToString(),
									zjxm_name = reader["zjxm_name"].ToString(),
									zjxm_sx_name = reader["zjxm_sx_name"].ToString(),
									zjxm_jybz = reader["zjxm_jybz"].ToString(),
									zjxm_remark = reader["zjxm_remark"].ToString()
								});
							}
						}
					}
				}
				catch (Exception ex)
				{
					// 记录日志或抛出异常
					throw new ApplicationException("An error occurred while fetching data from the database.", ex);
				}
				finally
				{
					connection.Close();
				}
			}

			return zjxm;
		}

		/// <summary>
		/// 根据产品编码查询产品
		/// </summary>
		/// <param name="cp_code"></param>
		/// <returns></returns>
		public QMS_ZJXM GetQMS_ZJXMzjxm_code(string zjxm_code)
		{
			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = $" SELECT xm.zjxm_id,xm.zjxm_code,xm.zjxm_name,d1.dictionary_name as zjxm_sx_name,\r\n xm.zjxm_jybz,xm.zjxm_max,xm.zjxm_min,dw.dw_name as zjxm_dw_name,xm.zjxm_remark\r\n FROM qms_zjxm as xm \r\n INNER JOIN  bd_dw as dw ON xm.zjxm_dw = dw.dw_id\r\n INNER JOIN  dictionary as d1 ON d1.dictionary_id = xm.zjxm_sx\r\nwhere zjxm_isdelete=1 and xm.zjxm_state=1 and zjxm_code='{zjxm_code}'";
				QMS_ZJXM zjxm = connection.Query<QMS_ZJXM>(sql).FirstOrDefault(); // 使用 ToList() 获取所有菜单  
				return zjxm;
			}
		}

		/// <summary>
		/// 改变状态
		/// </summary>
		/// <param name="id"></param>
		/// <param name="state"></param>
		/// <returns></returns>
		public bool StateChange(int id, int state)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"update [dbo].[qms_zjxm] set [zjxm_state]=@State where [zjxm_id]=@Id";
					return connection.Execute(sql, new { Id = id, State = state }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"状态更改失败: {ex.Message}");
				return false;
			}
		}


		/// <summary>
		/// 新增
		/// </summary>
		/// <param name="zjxm"></param>
		/// <returns></returns>
		public bool AddQMS_ZJXM(QMS_ZJXM zjxm)
		{
			string sql = @"INSERT INTO qms_zjxm (
zjxm_code,zjxm_name,zjxm_sx,zjxm_jybz,zjxm_max,zjxm_min,zjxm_dw,zjxm_remark,zjxm_create_by
) VALUES 
(@code,@name,@sx,@jybz,@max,@min,@dw,@remark,1);";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@code", zjxm.zjxm_code);
						command.Parameters.AddWithValue("@name", zjxm.zjxm_name);
						command.Parameters.AddWithValue("@sx", zjxm.zjxm_sx);
						command.Parameters.AddWithValue("@jybz", zjxm.zjxm_jybz);
						command.Parameters.AddWithValue("@max", zjxm.zjxm_max);
						command.Parameters.AddWithValue("@min", zjxm.zjxm_min);
						command.Parameters.AddWithValue("@dw", zjxm.zjxm_dw);
						command.Parameters.AddWithValue("@remark", zjxm.zjxm_remark);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}


		/// <summary>
		/// 修改
		/// </summary>
		/// <param name="zjxm"></param>
		/// <returns></returns>
		public bool UpdateQMS_ZJXM(QMS_ZJXM zjxm)
		{
			string sql = @"update [dbo].[qms_zjxm] set
zjxm_name=@name,zjxm_sx=@sx,zjxm_jybz=@jybz,zjxm_max=@max,zjxm_min=@min,
zjxm_dw=@dw,zjxm_remark=@remark,[zjxm_update_by]=1,[zjxm_update_time]=GETDATE()
where [zjxm_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@name", zjxm.zjxm_name);
						command.Parameters.AddWithValue("@sx", zjxm.zjxm_sx);
						command.Parameters.AddWithValue("@jybz", zjxm.zjxm_jybz);
						command.Parameters.AddWithValue("@max", zjxm.zjxm_max);
						command.Parameters.AddWithValue("@min", zjxm.zjxm_min);
						command.Parameters.AddWithValue("@dw", zjxm.zjxm_dw);
						command.Parameters.AddWithValue("@remark", zjxm.zjxm_remark);
						command.Parameters.AddWithValue("@id", zjxm.zjxm_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}


		/// <summary>
		/// 删除
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelQMS_ZJXM(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[qms_zjxm] 
                       SET zjxm_isdelete = 0 
                       WHERE zjxm_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}


		/// <summary>
		/// 批量删除
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelQMS_ZJXMs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[qms_zjxm] 
		                                 SET zjxm_isdelete = 0 
		                                 WHERE zjxm_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}
	}
}
